Imports System.data.SqlClient
Imports System.Text

Friend Class DBView_Factory
    Inherits DBBase

    Public Function GetAllViewsForDatabase( _
                    ByVal lDatabaseID As Long, _
                    ByVal sStartDate As String, _
                    ByVal sEndDate As String, _
                    ByVal lSort As View_Factory.SORT) As Collection

        Dim oList As New Collection

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("     a.[ID],")
            .Append("     a.[RevID]")
            .Append(" from")
            .Append("     tDDViews a")
            .Append(" where")
            .Append("     a.[DatabaseID]=" & ValueToDBValue(lDatabaseID))
            .Append("     and a.[IsDeleted]=0")
            .Append("     and a.[RevID]=(")
            .Append("     select")
            .Append("         max(b.[RevID])")
            .Append("     from")
            .Append("         tDDViews b")
            .Append("     where")
            .Append("         b.[ID]=a.[ID]")
            .Append("         and isnull(b.[changedate],b.[createdate]) between " & ValueToDBValue(sStartDate) & " and " & ValueToDBValue(sEndDate))
            .Append("         and b.[IsDeleted]=0)")
            .Append(" order by")

            Select Case lSort
                Case View_Factory.SORT.DATE_ASC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) asc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.DATE_DESC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) desc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.NAME_ASC
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.NAME_DESC
                    .Append("     a.[Name] desc")

                Case View_Factory.SORT.VER_ASC
                    .Append("     a.[RevID] asc,")
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.VER_DESC
                    .Append("     a.[RevID] desc,")
                    .Append("     a.[Name] asc")

            End Select
        End With

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL.ToString)

        For Each oRow As DataRow In oData.Rows
            Dim oView As New SQLAutoDocLib.View(oRow.Item(0), oRow.Item(1))
            oView.Load()

            oList.Add(oView, oView.ID)
        Next

        oData.Dispose()

        Return oList
    End Function

    Public Function GetAllViewsForDatabase( _
                        ByVal lDatabaseID As Long, _
                        ByVal lSort As View_Factory.SORT) As Collection

        Dim oList As New Collection

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("     a.[ID],")
            .Append("     a.[RevID]")
            .Append(" from")
            .Append("     tDDViews a")
            .Append(" where")
            .Append("     a.[DatabaseID]=" & ValueToDBValue(lDatabaseID))
            .Append("     and a.[IsDeleted]=0")
            .Append("     and a.[RevID]=(")
            .Append("     select")
            .Append("         max(b.[RevID])")
            .Append("     from")
            .Append("         tDDViews b")
            .Append("     where")
            .Append("         b.[ID]=a.[ID]")
            .Append("         and b.[IsDeleted]=0)")
            .Append(" order by")

            Select Case lSort
                Case View_Factory.SORT.DATE_ASC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) asc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.DATE_DESC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) desc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.NAME_ASC
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.NAME_DESC
                    .Append("     a.[Name] desc")

                Case View_Factory.SORT.VER_ASC
                    .Append("     a.[RevID] asc,")
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.VER_DESC
                    .Append("     a.[RevID] desc,")
                    .Append("     a.[Name] asc")

            End Select
        End With

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL.ToString)

        For Each oRow As DataRow In oData.Rows
            Dim oView As New SQLAutoDocLib.View(oRow.Item(0), oRow.Item(1))
            oView.Load()

            oList.Add(oView, oView.ID)
        Next

        oData.Dispose()

        Return oList
    End Function

    Public Function GetAllViewsForDatabase( _
                           ByVal lDatabaseID As Long, _
                           ByVal lDatabaseRevID As Long, _
                           ByVal lSort As View_Factory.SORT) As Collection

        Dim oList As New Collection

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("     a.[ID]")
            .Append(" from")
            .Append("     tDDViews a")
            .Append(" where")
            .Append("     a.[DatabaseID]=" & ValueToDBValue(lDatabaseID))
            .Append("     and a.[IsDeleted]=0")
            .Append("     and a.[RevID]=(")
            .Append("     select")
            .Append("         max(b.[RevID])")
            .Append("     from")
            .Append("         tDDViews b")
            .Append("     where")
            .Append("         b.[ID]=a.[ID]")
            .Append("     	and b.[DatabaseRevID]<=" & ValueToDBValue(lDatabaseRevID) & ")")
            .Append(" order by")

            Select Case lSort
                Case View_Factory.SORT.DATE_ASC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) asc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.DATE_DESC
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate]) desc,")
                    .Append("     a.[Name]")

                Case View_Factory.SORT.NAME_ASC
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.NAME_DESC
                    .Append("     a.[Name] desc")

                Case View_Factory.SORT.VER_ASC
                    .Append("     a.[RevID] asc,")
                    .Append("     a.[Name] asc")

                Case View_Factory.SORT.VER_DESC
                    .Append("     a.[RevID] desc,")
                    .Append("     a.[Name] asc")

            End Select
        End With

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL.ToString)

        For Each oRow As DataRow In oData.Rows
            Dim oView As New SQLAutoDocLib.View(oRow.Item(0))
            oView.Load()

            oList.Add(oView, oView.ID)
        Next

        oData.Dispose()

        Return oList
    End Function

    Public Function GetViewByName( _
                        ByVal lDatabaseID As Long, _
                        ByVal lOwnerID As Long, _
                        ByVal sView As String) As SQLAutoDocLib.View

        Dim oView As SQLAutoDocLib.View = Nothing

        Dim sSQL As New StringBuilder("select top 1")
        With sSQL
            .Append("     [ID]")
            .Append(" from")
            .Append("     tDDViews")
            .Append(" where")
            .Append("     [DatabaseID]=" & ValueToDBValue(lDatabaseID))
            .Append("     and [OwnerID]=" & ValueToDBValue(lOwnerID))
            .Append("     and [Name]=" & ValueToDBValue(sView))
            .Append(" order by")
            .Append("     [RevID] desc")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If IsNumeric(oValue) Then
            oView = New SQLAutoDocLib.View(CLng(oValue))
            oView.Load()
        End If

        Return oView
    End Function

    Public Function GetAllViewRevs( _
        ByVal lViewID As Long) As ArrayList

        Dim oList As New ArrayList

        Dim sSQL As New StringBuilder("select")
        With sSQL
            .Append("     [RevID]")
            .Append(" from")
            .Append("     tDDViews")
            .Append(" where")
            .Append("     [ID]=" & ValueToDBValue(lViewID))
            .Append(" order by")
            .Append("     [RevID] desc")
        End With

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL.ToString)

        For Each oRow As DataRow In oData.Rows
            Dim oView As New SQLAutoDocLib.View(lID:=lViewID, lrevid:=oRow.Item(0))
            oView.Load()

            oList.Add(oView)
        Next

        oData.Dispose()

        Return oList
    End Function

    Public Function FindViews( _
            ByVal lServerID As Long, _
            ByVal lDatabaseID As Long, _
            ByVal sStartDate As String, _
            ByVal sEndDate As String, _
            ByVal sSearchText As String) As ArrayList

        Dim oList As New ArrayList

        Dim bUseAnd As Boolean = False

        Dim sSQL As New StringBuilder("select distinct")
        With sSQL
            .Append("     a.[ID],")
            .Append("     a.[RevID],")
            .Append("     a.[Name]")
            .Append(" from")
            .Append("     tDDViews a")

            If sSearchText > "" Then
                .Append("         join tDDViewText b on b.[ViewID]=a.[ID] ")
                .Append("                                and b.[ViewRevID]=a.[RevID]")
            End If

            If lServerID <> DBBase.NODATA Then
                .Append("         join tDDDatabases c on c.[ID]=a.[DatabaseID] ")
            End If

            If lServerID <> DBBase.NODATA _
                OrElse lDatabaseID <> DBBase.NODATA _
                OrElse IsDate(sStartDate) _
                OrElse IsDate(sEndDate) _
                OrElse sSearchText > "" Then

                .Append(" where")

                If lServerID <> DBBase.NODATA Then
                    .Append("     c.[ServerID]=" & lServerID)
                    bUseAnd = True
                End If

                If lDatabaseID <> DBBase.NODATA Then
                    If bUseAnd Then .Append(" and ") Else bUseAnd = True
                    .Append("     a.[DatabaseID]=" & lDatabaseID)
                End If

                If IsDate(sStartDate) Then
                    If bUseAnd Then .Append(" and ") Else bUseAnd = True
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate])>=" & ValueToDBValue(sStartDate))
                End If

                If IsDate(sEndDate) Then
                    If bUseAnd Then .Append(" and ") Else bUseAnd = True
                    .Append("     isnull(a.[ChangeDate],a.[CreateDate])<=" & ValueToDBValue(sEndDate))
                End If

                If sSearchText > "" Then
                    If bUseAnd Then .Append(" and ") Else bUseAnd = True
                    .Append("(")
                    .Append(" a.[Name] like '%" & sSearchText.Replace("'", "''") & "%' ")
                    .Append(" or b.[Text] like '%" & sSearchText.Replace("'", "''") & "%' ")
                    .Append(")")
                End If
            End If

            .Append(" order by")
            .Append("     a.[Name],")
            .Append("     a.[RevID] desc")
        End With

        Dim oData As DataTable = MyBase.ExecuteReadToDataTable(sSQL.ToString)

        For Each oRow As DataRow In oData.Rows
            Dim oView As New SQLAutoDocLib.View(lID:=oRow.Item("ID"), lrevid:=oRow.Item("RevID"))
            oView.Load()

            oList.Add(oView)
        Next

        oData.Dispose()

        Return oList
    End Function

    Public Function CountOfActiveViewsForDatabase(ByVal lDatabaseID As Long) As Integer
        Dim iCount As Integer = 0

        Dim sSQL As New StringBuilder("select ")
        With sSQL
            .Append("     count(distinct [ID])")
            .Append(" from")
            .Append("     tDDViews")
            .Append(" where")
            .Append("     [DatabaseID]=" & ValueToDBValue(lDatabaseID))
            .Append("     and [IsDeleted]=0")
        End With

        Dim oValue As Object = MyBase.ExecuteScalar(sSQL.ToString)

        If IsNumeric(oValue) Then
            iCount = Val(oValue)
        End If

        Return iCount
    End Function
End Class
